/*==================================================
project:       Cleans up SISBEN for use
Author:        David L. Vargas 
E-email:       davidvar@iadb.org
url:           
Dependencies:  
----------------------------------------------------
Creation Date:    15 Aug 2022 - 11:01:26
Modification Date:   
Do-file version:    01
References:          
Output:           A yearly level dataset 
==================================================*/


 /*==================================================
               0: Program set up
 ==================================================*/
 *Written on STATA 17
 drop _all
 set varabbrev off	// no variable abbreviations allowed (personal preference)

 /*==================================================
               1: load and transformations
 ==================================================*/

 *---------- 1.1: Load data 
 use "${dir3r}/01_survey/replica/survey_targeting_r1.dta", replace
 
 /*==================================================
               2: Shape long 
 ==================================================*/
 
 *============= 2.1: Identify the variables that change through time
 
 *------- 2.1.1: labour variables
 
 * 2021
 rename labour_01 		work_p_2021 		   // "Paid work at least one hour past month - breadwinner"	
 rename labour_02 		unpaid_2021 		   // "Unpaid work at least one hour past month - breadwinner"	
 rename labour_03 		occupation_des_2021    // "Occupation description - breadwinner"	
 rename labour_04 		pension_2021 		   // "Contributes to pension fund - breadwinner"	
 rename labour_05 		occupation_type_2021   // "Type of occupation - breadwinner"	
 rename labour_05* 		occupation_type_*_2021 // "Type of occupation - breadwinner"	
 rename labour_06 		sector_2021			   // "Company's sector - breadwinner"	
 rename labour_06* 		sector_*_2021		   // "Company's sector - breadwinner"	
 rename labour_07 		place_work_2021	  	   // "Place of work - breadwinner"	
 rename labour_07* 		place_work_*_2021	   // "Place of work - breadwinner"	
 *rename labour_07_ot 	place_work_ot_2021	   // "Place of work other - breadwinner"
 rename labour_08 		work_2021 			   // "Worked for a wage or self-employed in 2020 - breadwinner"
 rename labour_09 		job_loss_2021  		   // "Involuntary job lost in 2020 - breadwinner"
 rename labour_10 		job_loss_m_2021		   // "Month of job lost 2020 - breadwinner" // this is a trick variable ENCUESTADOR: SI EXISTE MAS DE UN MES, SELECCIONAR ULTIMO MES
 
 * 2020
 rename labour_11 		 work_p_2020			// "Paid work at least one hour past month  - Nov 2020 - breadwinner"
 rename labour_12 		 unpaid_2020 			// "Unpaid work at least one hour past month - Nov 2020 - breadwinner"
 rename labour_13 		 work_same_2020			// "Same as current occupation - Nov 2020 - breadwinner"
 rename labour_14 		 occupation_des_2020  	// "Occupation description  - Nov 2020 - breadwinner"
 rename labour_15 		 pension_2020 		  	// "Contributes to pension - Nov 2020 - breadwinner"
 rename labour_16 		 occupation_type_2020 	// "Type of occupation - Nov 2020 - breadwinner"
 rename labour_16* 		 occupation_type_*_2020 // "Type of occupation - Nov 2020 - breadwinner"
 rename labour_17 		 sector_2020			// "Company's sector -  - Nov 2020 - breadwinner"
 rename labour_17* 		 sector_*_2020			// "Company's sector -  - Nov 2020 - breadwinner"
 rename labour_18 		 place_work_2020 		// "Place of work - Nov 2020 - breadwinner"
 rename labour_18* 		 place_work_*_2020 		// "Place of work - Nov 2020 - breadwinner"
 *rename labour_18_ot 	 place_work_ot_2020		// "Place of work other - Nov 2020 - breadwinner"
 rename labour_19 		 job_loss_2020  		// "Involuntary job lost in 2020 - breadwinner"
 rename labour_20 		 job_loss_m_2020		// "Month of job lost 2020 - breadwinner" // this is a trick variable ENCUESTADOR: SI EXISTE MAS DE UN MES, SELECCIONAR ULTIMO MES
 
 * 2019
 rename labour_21 		work_p_2019				// "Paid work at least one hour past month  - Nov 2019 - breadwinner"
 rename labour_22 		unpaid_2019 			// "Unpaid work at least one hour past month - Nov 2019 - breadwinner"
 rename labour_23 		work_same_2019			// "Same as Nov 2019 occupation - Nov 2019 - breadwinner"
 rename labour_24 		occupation_des_2019  	// "Occupation description  - Nov 2019 - breadwinner"
 rename labour_25 		pension_2019 		  	// "Contributes to pension - Nov 2019 - breadwinner"
 rename labour_26 		occupation_type_2019 	// "Type of occupation - Nov 2019 - breadwinner"
 rename labour_26* 		occupation_type_*_2019 	// "Type of occupation - Nov 2019 - breadwinner"
 rename labour_27 		place_work_2019			// "Place of work - Nov 2019 - breadwinner"
 rename labour_27* 		place_work_*_2019		// "Place of work - Nov 2019 - breadwinner"
 *rename labour_27_ot 	place_work_ot_2019 		// "Place of work other - Nov 2019 - breadwinner"
 rename labour_28		sector_2019				// "Company's sector -  - Nov 2019 - breadwinner"
 rename labour_28*		sector_*_2019			// "Company's sector -  - Nov 2019 - breadwinner"
 rename labour_29		job_loss_2019  			// "Involuntary job lost in 2019 - breadwinner"
 rename labour_30		job_loss_m_2019		 	// "Month of job lost 2019 - breadwinner" // this is a trick variable ENCUESTADOR: SI EXISTE MAS DE UN MES, SELECCIONAR ULTIMO MES
 
 
 *------- 2.1.2: income 
 
 * 2019
 rename income_20* 		income_zero_*_2019		// "No income- Jan 2019"   
 rename income_15 		income_2019				// "Total HH monthly income - 2019" 
 rename income_16 		income_cat_2019			// "Total HH monthly income category - 2019" 
 rename income_17b 		income_pension_2019		// "Monthly income from pension payments - 2019"   
 rename income_17c 		income_trans_2019		// "Monthly income from goverment programs - 2019"   
 rename income_18 		income_loss_2019		// "Income below one-half of the usual monthly income any given month - 2019" 
 rename income_19* 		income_loss_*_2019		// "Income below one-half - Jan 2019"   
 rename income_21* 		income_loss_rs_*_2019	// "Reason of income reduction 2019 - worked less"   
 
 * 2020
 rename income_08 		income_2020				// "Total HH monthly income - 2020" 
 rename income_09 		income_cat_2020			// "Total HH monthly income category - 2020"
 rename income_10b 		income_pension_2020		// "Monthly income from pension payments - 2020"  
 rename income_10c 		income_trans_2020		// "Monthly income from goverment programs - 2020"  
 rename income_11 		income_loss_2020		// "Income below one-half of the usual monthly income any given month - 2020"
 rename income_12* 		income_loss_*_2020		// "Income below one-half - Jan 2020"  
 rename income_13* 		income_zero_*_2020		// "No income- Jan 2020"  
 rename income_14* 		income_loss_rs_*_2020	// "Reason of income reduction 2020 - worked less"  
 
 * 2021
 rename income_01 		income_2021			// "Total HH monthly income - 2021" 
 rename income_02 		income_cat_2021		// "Total HH monthly income category - 2021" 
 rename income_03b		income_pension_2021	// "Monthly income from pension payments - 2021"  
 rename income_03c		income_trans_2021	// "Monthly income from goverment programs - 2021"  
 rename income_04 		income_loss_2021	// "Income below one-half of the usual monthly income any given month - 2021" 
 rename income_05*		income_loss_*_2021	// "Income below one-half - Jan 2021"  
 rename income_06*		income_zero_*_2021	// "No income- Jan 2021"  
 rename income_07*		income_loss_rs_*_2021	// "Reason of income reduction 2021 - worked less"  
 
 loc k = 0
 foreach t in "15" "08" "01"  {
	loc y = 2019+`k'

	rename income_`t'_pp		income_pp_`y'
	rename income_`t'_pp_nt		income_pp_nt_`y'
	rename income_`t'_pp_win	income_pp_win_`y'
	rename income_`t'_pp_ppp	income_pp_ppp_`y'
	rename income_`t'_pp_nt_win	income_pp_nt_win_`y'  
	rename income_`t'_pp_nt_ppp	income_pp_nt_ppp_`y' 
	loc ++k	
 }
 
 *------- 2.1.3: foodsec
 
 rename foodsec_01 	foodsec_2019	// "Has gone hungry because of lack of resources - 2019"
 rename foodsec_02*	foodsec_*_2019	// "Went hungry - Jan 2019" 

 rename foodsec_03 	foodsec_2020	// "Has gone hungry because of lack of resources - 2020"
 rename foodsec_04*	foodsec_*_2020	// "Went hungry - Jan 2020"  

 rename foodsec_05 	foodsec_2021	// "Has gone hungry because of lack of resources - 2021"
 rename foodsec_06*	foodsec_*_2021	// "Went hungry - Jan 2021"  
  
 *------ 2.1.4: Delays
 
 * internet + (comms)
 rename delays_00aa 	delay_comms_2019	 		// "Interuption of internet, cellphone or electricity - 2019"
 rename delays_00ab 	delay_comms_2020	 		// "Interuption of internet, cellphone or electricity - 2020"
 rename delays_00ac 	delay_comms_2021	 		// "Interuption of internet, cellphone or electricity - 2021"
 drop delays_00ad 							 		// "Interuption of internet, cellphone or electricity - Never"
 drop delays_00ae 							 		// "Interuption of internet, cellphone or electricity - DNK"		
 rename delays_01 		delays_comms_money_2019		// "Interuption due to lack of resources - 2019 - internet, cell, electricity"	
 rename delays_02* 		delays_comms_money_*_2019	// "Interuption (internet+) money related - Jan 2019"
 rename delays_03 		delays_comms_money_2020		// "Interuption due to lack of resources - 2020 - internet, cell, electricity"	
 rename delays_04* 		delays_comms_money_*_2020	// "Interuption (internet+) money related - Jan 2020"	
 rename delays_05 		delays_comms_money_2021		// "Interuption due to lack of resources - 2021 - internet, cell, electricity"		
 rename delays_06* 		delays_comms_money_*_2021	// "Interuption (internet+) money related - Jan 2021"	
 
 * medical
 rename delays_07aa 	delay_medical_2019				// "Lack of medical attention or medicines - 2019"		
 rename delays_07ab 	delay_medical_2020				// "Lack of medical attention or medicines - 2020"		
 rename delays_07ac 	delay_medical_2021				// "Lack of medical attention or medicines - 2021"		
 drop delays_07ad 										// "Lack of medical attention or medicines - Never"		
 drop delays_07ae 										// "Lack of medical attention or medicines - DNK"				
 rename delays_07 		delays_medical_money_2019		// "Lack of medical attention due to insuficient resources - 2019"	
 rename delays_08* 		delays_medical_money_*_2019		// "Lack of medical attention (-money)- Jan 2019"		
 rename delays_09 		delays_medical_money_2020		// "Lack of medical attention due to insuficient resources - 2020"	
 rename delays_10* 		delays_medical_money_*_2020		// "Lack of medical attention (-money) - Jan 2020"		
 rename delays_11 		delays_medical_money_2021		// "Lack of medical attention due to insuficient resources - 2021"	
 rename delays_12* 		delays_medical_money_*_2021		// "Lack of medical attention (-money) - Jan 2021"			
 
 * Rent
 rename delays_13 		delays_rent_2019			// "Delayed or no rent payment due to insuficient resources - 2019"	
 rename delays_13_det 	delays_rent_det_2019				// "Delayed or no rent payment insuficient resources - 2019 (detail)"	
 rename delays_14* 		delays_rent_*_2019			// "Delayed or no rent payment (-money) - Jan 2019"				
 
 rename delays_15 		delays_rent_2020		// "Delayed or no rent payment due to insuficient resources - 2020"	
 rename delays_15_det 	delays_rent_det_2020			// 	"Delayed or no rent payment insuficient resources - 2020 (detail)"	
 rename delays_16* 		delays_rent_*_2020		// "Delayed or no rent payment (-money) - Jan 2020"				
 
 rename delays_17 		delays_rent_2021		// "Delayed or no rent payment due to insuficient resources - 2021"	
 rename delays_17_det	delays_rent_det_2021			// 	"Delayed or no rent payment insuficient resources - 2021 (detail)"	
 rename delays_18* 		delays_rent_*_2021		// "Delayed or no rent payment (-money) - Jan 2021"					
 
 * Debt service
 rename delays_19 		delays_debt_2019		// "Delayed or no debt payment due to insuficient resources - 2019"
 rename delays_19_det	delays_debt_det_2019			// 	"Delayed or no debt payment insuficient resources - 2019 (detail)"
 rename delays_20* 		delays_debt_*_2019		// "Delayed or no debt payment (-money) - Jan 2019"					
 
 rename delays_21 		delays_debt_2020		// "Delayed or no debt payment due to insuficient resources - 2020"
 rename delays_21_det 	delays_debt_det_2020			// "Delayed or no debt payment insuficient resources - 2020 (detail)"
 rename delays_22* 		delays_debt_*_2020		// "Delayed or no debt payment (-money) - Jan 2020"						
 
 rename delays_23 		delays_debt_2021		// "Delayed or no debt payment due to insuficient resources - 2021"
 rename delays_23_det 	delays_debt_det_2021			// "Delayed or no debt payment insuficient resources - 2021 (detail)"
 rename delays_24*		delays_debt_*_2021		// "Delayed or no debt payment (-money) - Jan 2021"					
 
 *------ 2.1.5: Assets
 
 /* Build measures of ownership by year .... I have to think this trough... */
 
  * Appropiate renaming and labelling
 tokenize `c(alpha)'
 glo ord "" // global macro for ordering 
 forv i = 1/8 {
 	loc k = "``i''"
	
	* if (`i' == 1) loc a = "Refrigerator or fridge"
    * if (`i' == 2) loc a = "Washing machine"
    * if (`i' == 3) loc a = "Computer"
    * if (`i' == 4) loc a = "Tablet"
    * if (`i' == 5) loc a = "Smart phone"
    * if (`i' == 6) loc a = "Motorbike or scooter"
    * if (`i' == 7) loc a = "Tractor"
    * if (`i' == 8) loc a = "Car"
	
	rename assets_01`k'			assets_own_`k'			// "Household owns: `a'"
	rename assets_01`k'_pre		assets_pre_`k'			
	rename assets_01a`k'  		assets_buy_`k'			// "HH purchased since 2019: `a'" 
	rename assets_02`k'   		assets_buy_year_`k'		// "Year of purchase: `a'" 
	rename assets_02a`k'  		assets_buy_date_`k'	// "Date of purchase: `a' (if provided)" 
	rename assets_04`k'   		assets_sell_`k'			// "HH sold since 2019: `a'"
	rename assets_05`k'   		assets_sell_year_`k'	// "Year of sale: `a'" 
	rename assets_05a`k'  		assets_sell_date_`k'	// "Date of sale: `a' (if provided)" 

 }
 
 // Gen yearly variables 
 glo ordering  ""
 tokenize `c(alpha)'
 glo ord "" // global macro for ordering 
 /*forv i = 1/8 {
 	loc k = "``i''"	
	 
	 loc vlab: var label assets_own_`k'
	 loc vlab = subinstr("`vlab'", "Household owns:", "", .)
	 
	 glo orderinga ""
	 forv y = 2019/2021 {
		 
		 gen assets_own_`k'_`y' = assets_pre_`k'
		 gen assets_buy_`k'_`y' = (assets_buy_year_`k' <= 1	& assets_buy_`k' == 1)
		 gen assets_sell_`k'_`y' = (assets_sell_year_`k' <= 1	& assets_sell_`k' == 1)
		 gen assets_change_`k'_`y' = assets_buy_`k'_`y' - assets_sell_`k'_`y'
		 replace assets_own_`k'_`y' = assets_own_`k'_`y' + assets_change_`k'_`y'
		 replace assets_own_`k'_`y' = (assets_own_`k'_`y' > 0)
		 
		 
		 lab var assets_own_`k'_`y' 	"Owns `vlab'"
		 lab var assets_buy_`k'_`y' 	"Bought `vlab'"	 
		 lab var assets_sell_`k'_`y'	"Sold `vlab'"	 
		 lab var assets_change_`k'_`y'	"Change: `vlab'"
		 
		 glo orderinga " $orderinga assets_own_`k'_`y' assets_buy_`k'_`y' assets_sell_`k'_`y' assets_change_`k'_`y' assets_own_`k'_`y'"
		 
	 }
	 	 
	 glo ordering "$ordering assets_pre_`k' $orderinga assets_buy_year_`k' assets_buy_date_`k'  assets_sell_year_`k' assets_sell_date_`k'"
 }
 */
 
 forv i = 1/8 {
 	loc k = "``i''"	
	 
	 loc vlab: var label assets_own_`k'
	 loc vlab = subinstr("`vlab'", "Household owns:", "", .)
	 
	 glo orderinga ""
		
		 gen assets_own_`k'_2019 = assets_pre_`k'
		 gen assets_buy_`k'_2019 = (assets_buy_year_`k' <= 1	& assets_buy_`k' == 1)
		 gen assets_sell_`k'_2019 = (assets_sell_year_`k' <= 1	& assets_sell_`k' == 1)
		 gen assets_change_`k'_2019 = assets_buy_`k'_2019 - assets_sell_`k'_2019
		 replace assets_own_`k'_2019 = assets_own_`k'_2019 + assets_change_`k'_2019
		 replace assets_own_`k'_2019 = (assets_own_`k'_2019 > 0)
		 
		 gen assets_buy_`k'_2020 = (assets_buy_year_`k' == 2	& assets_buy_`k' == 1)
		 gen assets_sell_`k'_2020 = (assets_sell_year_`k' == 2	& assets_sell_`k' == 1)
		 gen assets_change_`k'_2020 = assets_buy_`k'_2020 - assets_sell_`k'_2020
		 g assets_own_`k'_2020 = assets_own_`k'_2019 + assets_change_`k'_2020
		 replace assets_own_`k'_2020 = (assets_own_`k'_2020 > 0)
		 
		 gen assets_buy_`k'_2021 = (assets_buy_year_`k'==3	& assets_buy_`k' == 1)
		 gen assets_sell_`k'_2021 = (assets_sell_year_`k' == 3	& assets_sell_`k' == 1)
		 gen assets_change_`k'_2021 = assets_buy_`k'_2021 - assets_sell_`k'_2021
		 g assets_own_`k'_2021 = assets_own_`k'_2020 + assets_change_`k'_2021
		 replace assets_own_`k'_2021 = (assets_own_`k'_2021 > 0)
	forv y = 2019/2021 {
		 
		 lab var assets_own_`k'_`y' 	"Owns `vlab'"
		 lab var assets_buy_`k'_`y' 	"Bought `vlab'"	 
		 lab var assets_sell_`k'_`y'	"Sold `vlab'"	 
		 lab var assets_change_`k'_`y'	"Change: `vlab'"
	
		 
		 glo orderinga " $orderinga assets_own_`k'_`y' assets_buy_`k'_`y' assets_sell_`k'_`y' assets_change_`k'_`y' assets_own_`k'_`y'"
		 
	 }
	 	 
	 glo ordering "$ordering assets_pre_`k' $orderinga assets_buy_year_`k' assets_buy_date_`k'  assets_sell_year_`k' assets_sell_date_`k'"
 }
 order $ordering , after(housing_36g)
 
 *------ 2.1.6: Losses 
 
 ** Illness 
 rename losses_01_01 	ilness_any
 rename losses_01_01a 	ilness_2019
 rename losses_01_01b   ilness_2020
 rename losses_01_01c   ilness_2021
 drop losses_01_01d   	
 drop losses_01_01e						
 
 *2019
 drop losses_02_01		// same as illness_2019
 rename losses_03_01* 	illness_*_2019
 rename losses_04_01	illness_value_2019
 
 *2020
 drop losses_05_01 		// same as illness_2020
 rename losses_06_01*	illness_*_2020
 rename losses_07_01	illness_value_2020
 
 *2021
 drop losses_08_01 		// same as illness_2021
 rename losses_09_01*	illness_*_2021
 rename losses_10_01	illness_value_2021
 
 ** Death
 rename losses_01_02 	death_any
 rename losses_01_02a 	death_2019
 rename losses_01_02b   death_2020
 rename losses_01_02c   death_2021
 drop losses_01_02d   	
 drop losses_01_02e		
 
 *2019
 drop losses_02_02		// same as death_2019
 rename losses_03_02* 	death_*_2019
 rename losses_04_02	death_value_2019
 
 *2020
 drop losses_05_02 		// same as death_2020
 rename losses_06_02*	death_*_2020
 rename losses_07_02	death_value_2020
 
 *2021
 drop losses_08_02 		// same as death_2021
 rename losses_09_02*	death_*_2021
 rename losses_10_02	death_value_2021
 
 
 ** Separation
 rename losses_01_03 	divorce_any
 rename losses_01_03a 	divorce_2019
 rename losses_01_03b   divorce_2020
 rename losses_01_03c   divorce_2021
 drop losses_01_03d   	
 drop losses_01_03e		
 
 *2019
 drop losses_02_03		// same as divorce_2019
 rename losses_03_03* 	divorce_*_2019
 rename losses_04_03	divorce_value_2019
 
 *2020
 drop losses_05_03 		// same as divorce_2020
 rename losses_06_03*	divorce_*_2020
 rename losses_07_03	divorce_value_2020
 
 *2021
 drop losses_08_03 		// same as divorce_2021
 rename losses_09_03*	divorce_*_2021
 rename losses_10_03	divorce_value_2021
 
 
 ** bankrupt
 rename losses_01_04 	bankrupt_any
 rename losses_01_04a 	bankrupt_2019
 rename losses_01_04b   bankrupt_2020
 rename losses_01_04c   bankrupt_2021
 drop losses_01_04d   	
 drop losses_01_04e		
 
 *2019
 drop losses_02_04		// same as bankrupt_2019
 rename losses_03_04* 	bankrupt_*_2019
 rename losses_04_04	bankrupt_value_2019
 
 *2020
 drop losses_05_04		// same as bankrupt_2020
 rename losses_06_04*	bankrupt_*_2020
 rename losses_07_04	bankrupt_value_2020
 
 *2021
 drop losses_08_04 		// same as bankrupt_2021
 rename losses_09_04*	bankrupt_*_2021
 rename losses_10_04	bankrupt_value_2021
 
 
 ** cut remittance
 rename losses_01_05 	cut_remittance_any
 rename losses_01_05a 	cut_remittance_2019
 rename losses_01_05b   cut_remittance_2020
 rename losses_01_05c   cut_remittance_2021
 drop losses_01_05d   	
 drop losses_01_05e		
 
 *2019
 drop losses_02_05		// same as cut_remittance_2019
 rename losses_03_05* 	cut_remittance_*_2019
 rename losses_04_05	cut_remittance_value_2019
 
 *2020
 drop losses_05_05 		// same as cut_remittance_2020
 rename losses_06_05*	cut_remittance_*_2020
 rename losses_07_05	cut_remittance_value_2020
 
 *2021
 drop losses_08_05 		// same as cut_remittance_2021
 rename losses_09_05*	cut_remittance_*_2021
 rename losses_10_05	cut_remittance_value_2021
 
 
 ** Theft 
 rename losses_01_06 	theft_any
 rename losses_01_06a 	theft_2019
 rename losses_01_06b   theft_2020
 rename losses_01_06c   theft_2021
 drop losses_01_06d   	
 drop losses_01_06e		
 
 *2019
 drop losses_02_06		// same as theft_2019
 rename losses_03_06* 	theft_*_2019
 rename losses_04_06	theft_value_2019
 
 *2020
 drop losses_05_06 		// same as theft_2020
 rename losses_06_06*	theft_*_2020
 rename losses_07_06	theft_value_2020
 
 *2021
 drop losses_08_06 		// same  as theft_2021
 rename losses_09_06*	theft_*_2021
 rename losses_10_06	theft_value_2021
 
 
 ** conflict 
 rename losses_01_07 	conflict_any
 rename losses_01_07a 	conflict_2019
 rename losses_01_07b   conflict_2020
 rename losses_01_07c   conflict_2021
 drop losses_01_07d   	
 drop losses_01_07e		
 
 *2019
 drop losses_02_07		// same as conflict_2019
 rename losses_03_07* 	conflict_*_2019
 rename losses_04_07	conflict_value_2019
 
 *2020
 drop losses_05_07 		// same as conflict_2020
 rename losses_06_07*	conflict_*_2020
 rename losses_07_07	conflict_value_2020
 
 *2021
 drop losses_08_07 		// same as conflict_2021
 rename losses_09_07*	conflict_*_2021
 rename losses_10_07	conflict_value_2021
 
 
 ** Fires 
 rename losses_01_08 	fire_any
 rename losses_01_08a 	fire_2019
 rename losses_01_08b   fire_2020
 rename losses_01_08c   fire_2021
 drop losses_01_08d   	
 drop losses_01_08e		
 
 *2019
 drop losses_02_08		// same as fire_2019
 rename losses_03_08* 	fire_*_2019
 rename losses_04_08	fire_value_2019
 
 *2020
 drop losses_05_08 		// same as fire_2020
 rename losses_06_08*	fire_*_2020
 rename losses_07_08	fire_value_2020
 
 *2021
 drop losses_08_08 		// same as fire_2021
 rename losses_09_08*	fire_*_2021
 rename losses_10_08	fire_value_2021
 
 
 ** Natural disaster 
 rename losses_01_09 	nat_disaster_any
 rename losses_01_09a 	nat_disaster_2019
 rename losses_01_09b   nat_disaster_2020
 rename losses_01_09c   nat_disaster_2021
 drop losses_01_09d   	
 drop losses_01_09e		
 
 *2019
 drop losses_02_09		// same as nat_disaster_2019
 rename losses_03_09* 	nat_disaster_*_2019
 rename losses_04_09	nat_disaster_value_2019
 
 *2020
 drop losses_05_09 		// same as nat_disaster_2020
 rename losses_06_09*	nat_disaster_*_2020
 rename losses_07_09	nat_disaster_value_2020
 
 *2021
 drop losses_08_09 		// same as nat_disaster_2021
 rename losses_09_09*	nat_disaster_*_2021
 rename losses_10_09	nat_disaster_value_2021
 
 
 *------ 2.1.7: Credit

 rename credit_01a 		credit_app_2019			// "Applied for a formal credit - 2019" 
 rename credit_01b 		credit_app_2020			// "Applied for a formal credit - 2020" 
 rename credit_01c 		credit_app_2021			// "Applied for a formal credit - 2021" 
 drop credit_01d 								// "Applied for a formal credit - None" 
 drop credit_01e 								// "Applied for a formal credit - DNK" 
 
 * 2019
 drop credit_02									// "Applied for a formal credit in 2019" - same as credit_01a
 rename credit_03* 		credit_app_*_2019		// "Applied for a formal credit - Jan 2019"						
 rename credit_04		credit_approved_2019	// "Applied to credit and got approved - 2019"
 rename credit_04a*		credit_approved_*_2019	// "Credit approved - Jan 2019"
 
 * 2020
 drop credit_05									// 	"Applied for a formal credit in 2020"
 rename credit_06*		credit_app_*_2020		// "Applied for a formal credit- Jan 2020"
 rename credit_07		credit_approved_2020	// 	"Applied to credit and got approved - 2020"
 rename credit_07a*		credit_approved_*_2020	// "Credit approved - Jan 2020"
 
 *2021
 drop credit_08									// 	"Applied for a formal credit in 2021" - same as credit_01a
 rename credit_09*		credit_app_*_2021		// "Applied for a formal credit - Jan 2021"		
 rename credit_10		credit_approved_2021	// "Applied to credit and got approved - 2021"
 rename credit_10a*		credit_approved_*_2021	// "Credit approved - Jan 2021"
 
 * payments 
 rename credit_11		credit_active			// 	"Active credit during 1st Jan 2019, onwards"
 rename credit_12		credit_active_ontime	// 	"Meet all regular credit payments since 1st Jan 2019"
 
 *2019
 rename credit_13		credit_ontime_2019		// 	"Meet credit payments - 2019"
 rename credit_13_det	credit_ontime_det_2019	// 	"Meet credit payments - 2019"
 rename credit_14*		credit_default_*_2019	// "Did not meet credit payments - Jan 2019"
 *2020
 rename credit_15		credit_ontime_2020		// 	"Meet credit payments - 2020"
 rename credit_15_det	credit_ontime_det_2020	// 	"Meet credit payments - 2020"
 rename credit_16*		credit_default_*_2020	// "Did not meet credit payments - Jan 2020"
 *2021
 rename credit_17		credit_ontime_2021		// 	"Meet credit payments - 2021"
 rename credit_17_det	credit_ontime_det_2021	// 	"Meet credit payments - 2021"
 rename credit_18*      credit_default_*_2021   //   "Did not meet credit payments - Jan 2021"
 
 *------ 2.1.7: Fin
 
 ** savings  
 *2019
 rename fin_01			fin_use_savings_2019		// 	"Used savings to meet any of the HH basic needs - 2019"
 rename fin_01_det		fin_use_savings_det_2019	// 	"Used savings to meet any of the HH basic needs - 2019 (detail)"
 rename fin_02*			fin_use_savings_*_2019		// "Used savings to meet basic needs - Jan 2019"
 *2020
 rename fin_03			fin_use_savings_2020		// 	"Used savings to meet any of the HH basic needs - 2020"
 rename fin_03_det		fin_use_savings_det_2020	// 	"Used savings to meet any of the HH basic needs - 2020 (detail)"
 rename fin_04*			fin_use_savings_*_2020		// "Used savings to meet basic needs - Jan 2020"
 *2021
 rename fin_05			fin_use_savings_2021		// 	"Used savings to meet any of the HH basic needs - 2021"
 rename fin_05_det		fin_use_savings_det_2021	// 	"Used savings to meet any of the HH basic needs - 2021 (detail)"
 rename fin_06*			fin_use_savings_*_2021		// "Used savings to meet basic needs - Jan 2021"		
 
 ** family loan
 rename fin_07a			fin_fam_loan_2019			// "Family loan  - 2019"		
 rename fin_07b			fin_fam_loan_2020			// "Family loan  - 2020"		
 rename fin_07c			fin_fam_loan_2021			// "Family loan  - 2021"		
 drop fin_07d										// "Family loan  - Never"		
 drop fin_07e										// "Family loan  - DNK"		
 
 *2019
 drop fin_08											// same asfin_fam_loan_2019	// "Family loan to meet basic needs - 2019"	
 rename fin_09*			fin_fam_loan_*_2019				// "Family loan to meet basic needs - Jan 2019"	
 rename fin_10			fin_fam_loan_ontime_2019		// "Able to meet family loans payments - 2019"
 rename fin_11*			fin_fam_loan_default_*_2019		// "Not able to payback family loan - Jan 2019"	
 
 *2020
 drop fin_12											// 	"Family loan to meet basic needs - 2020"	
 rename fin_13*			fin_fam_loan_*_2020				// "Family loan to meet basic needs - Jan 2020"	
 rename fin_14			fin_fam_loan_ontime_2020		// 	"Able to meet family loans payments - 2020"	
 rename fin_15*			fin_fam_loan_default_*_2020		// "Not able to payback family loan - Jan 2020"		
 
 *2021
 drop fin_16											// "Family loan to meet basic needs - 2021"	
 rename fin_17*     	fin_fam_loan_*_2021			    // "Family loan to meet basic needs - Jan 2021"	
 rename fin_18      	fin_fam_loan_ontime_2021	    // "Able to meet family loans payments - 2021"
 rename fin_19*     	fin_fam_loan_default_*_2021   	// "Not able to payback family loan - Jan 2021"	
 
 ** informal credit
 rename fin_20a			fin_informal_2019				// "Informal credit (moneylenders) - 2019"
 rename fin_20b			fin_informal_2020				// "Informal credit (moenylenders) - 2020" 			
 rename fin_20c			fin_informal_2021				// "Informal credit (moenylenders) - 2021" 	
 drop fin_20d											// same as fin_informal_2020	// "Informal credit (moenylenders) - Never" 	
 drop fin_20e											// same as fin_informal_2020	// "Informal credit (moenylenders) - DNK" 	
 
 *2019
 drop fin_21											// same as fin_informal_2019 // 	"Informal credit (moneylenders) - 2019"	
 rename fin_22aa    	fin_informal_ontime_2019   		// "Able to meet moneylenders payments - 2019"
 rename fin_22bb*   	fin_informal_defualt_*_2019   	// "Not able to payback moneylenders loan - Jan 2019"
 rename fin_22*			fin_informal_*_2019				// "Informal credit (moneylenders) - Jan 2019"	
 *2020
 drop fin_23      	    								// same as fin_informal_2020 // "Informal credit (moneylender) - 2020"	
 rename fin_24aa    	fin_informal_ontime_2020    	// "Able to meet moneylenders payments - 2020"	
 rename fin_24bb*   	fin_informal_defualt_*_2020 	// "Not able to payback moneylenders loan - Jan 2020"
 rename fin_24*     	fin_informal_*_2020			 	// "Informal credit (moneylender) - Jan 2020"
 *2021
 drop fin_25      	 									// same as fin_informal_2021   //   "Informal credit (moneylender) - 2021"	
 rename fin_26aa    	fin_informal_ontime_2021    	// "Able to meet moneylenders payments - 2021"	 
 rename fin_26bb*   	fin_informal_defualt_*_2021 	// "Not able to payback moneylenders loan - Jan 2021" 
 rename fin_26*     	fin_informal_*_2021			 	// "Informal credit (moneylender) - Jan 2021" 
 
 ** gifts
 rename fin_27a     	fin_gift_2019   // "Received food/money gift - 2019"
 rename fin_27b     	fin_gift_2020   // "Received food/money gift - 2020"
 rename fin_27c     	fin_gift_2021   // "Received food/money gift - 2021"
 drop fin_27d     	    				// "Received food/money gift - Never"
 drop fin_27e							// "Received food/money gift - DNK"	
 
 drop fin_28      	   					// same as fin_gift_2019 // "Received food/money gift - 2019"	
 rename fin_29*			fin_gift_*_2019	// "Received food/money gift - Jan 2019"
 drop fin_30 							// fin_gift_2020 // "Received food/money gift - 2020"	
 rename fin_31*			fin_gift_*_2020	// "Received food/money gift - Jan 2020"
 drop fin_32 							// fin_gift_2021 // "Received food/money gift - 2021"	
 rename fin_33*			fin_gift_*_2021	// "Received food/money gift - Jan 2021"
 
 
 *============= 2.2: Reshape

 *--------------- 2.2.1. store time invariant variables 
 
 **** Divide variables into more manegable macros
 
 *IDs
 glo ids id_hogar_SISBEN ID_hogar /*grupo_SISBEN*/ /*clasificador*/
 
 *Survey metadata 
 glo surv_meta estrato /*list_sample*/ region urb_rur recibe_subs cod_dpto /*hh_13_sisben hh_14_sisben*/ resultado cover_01 cover_02 cover_03 cover_05 cover_07 cover_07det cover_08 pondera
 //list_sample hh_13_sisben hh_14_sisben are admin data *restricted*
 *housing info (survey updated)
 glo housing housing_01 housing_02a housing_02b housing_02c housing_03 housing_04a housing_04b housing_05 housing_06 housing_07 housing_08 housing_13 housing_13a housing_13b housing_13c housing_13d housing_13e housing_14 housing_14a housing_14b housing_14c housing_14d housing_14e housing_14f housing_14g housing_14h housing_15 housing_15a housing_15b housing_15c housing_15d housing_15e housing_15f housing_16a housing_16b housing_16c housing_16d housing_16e housing_17 housing_18 housing_19 housing_19a housing_19b housing_19c housing_19d housing_19e housing_19own housing_20 housing_21 housing_22 housing_23 housing_23a housing_23b housing_23c housing_23d housing_23e housing_24 housing_24a housing_24b housing_25 housing_25a housing_25b housing_25c housing_26 housing_26a housing_26b housing_26c housing_26d housing_26e housing_26f housing_26g housing_26h housing_26i housing_27 housing_28 housing_29 housing_30 housing_31 housing_31a housing_31b housing_31c housing_31d housing_31e housing_31f housing_32 housing_32a housing_32b housing_32c housing_32d housing_32e housing_32f housing_32g housing_33 housing_34 housing_34a housing_34b housing_34c housing_34d housing_34e housing_35 housing_35a housing_35b housing_35c housing_36 housing_36a housing_36b housing_36c housing_36d housing_36e housing_36f housing_36g
 
 *demographics
 glo dems urban hh_01 hh_03 hh_male hh_female hh_04 hh_05 hh_resp_head hh_06 hh_07 hh_08 hh_09 hh_10 hh_10b hh_10c /*hh_13 hh_13a_dd hh_13b_mm hh_13c_aa*/ hh_14 hh_15 hh_16a hh_16_no hh_16_elementary hh_16_high hh_16_tertiary hh_16aa hh_16ab hh_16ac hh_16ad hh_16ae hh_16af hh_16ag hh_16ah hh_16ai hh_16b 
 // h13 are comment out as they are PII (DOB)

 * other kind of survey variables 
  *unab vars : *_any
 *di "`vars'"
 glo other_assets assets_pre_a assets_buy_year_a assets_buy_date_a assets_sell_year_a assets_sell_date_a assets_pre_b assets_buy_year_b assets_buy_date_b assets_sell_year_b assets_sell_date_b assets_pre_c assets_buy_year_c assets_buy_date_c assets_sell_year_c assets_sell_date_c assets_pre_d assets_buy_year_d assets_buy_date_d assets_sell_year_d assets_sell_date_d assets_pre_e assets_buy_year_e assets_buy_date_e assets_sell_year_e assets_sell_date_e assets_pre_f assets_buy_year_f assets_buy_date_f assets_sell_year_f assets_sell_date_f assets_pre_g assets_buy_year_g assets_buy_date_g assets_sell_year_g assets_sell_date_g assets_pre_h assets_buy_year_h assets_buy_date_h assets_sell_year_h assets_sell_date_h assets_own_a assets_buy_a assets_own_b assets_buy_b assets_own_c assets_buy_c assets_own_d assets_buy_d assets_own_e assets_buy_e assets_own_f assets_buy_f assets_own_g assets_buy_g assets_own_h assets_buy_h assets_sell_a assets_sell_b assets_sell_c assets_sell_d assets_sell_e assets_sell_f assets_sell_g assets_sell_h
 
 glo other_losses ilness_any death_any divorce_any bankrupt_any cut_remittance_any theft_any conflict_any fire_any nat_disaster_any
 
 glo other_credit credit_active credit_active_ontime

 *SISBEN varaibles (pre/controls)
 glo sisben id_vivienda id_persona n_kids_18 cookpower_connected living_couple prop_kids age_feb20 cod_clase  uso_vivienda  tip_vivienda tip_mat_paredes tip_mat_pisos ind_tiene_energia tip_estrato_energia ind_tiene_alcantarillado ind_tiene_gas ind_tiene_recoleccion ind_tiene_acueducto tip_estrato_acueducto num_cuartos_vivienda num_hogares_vivienda ide_hogar num_cuartos_exclusivos num_cuartos_dormir num_cuartos_unicos_dormir tip_sanitario tip_ubi_sanitario tip_uso_sanitario tip_origen_agua ind_agua_llega_7dias water_ndays ind_agua_llega_24horas water_nhours tip_uso_agua_beber tip_elimina_basura ind_tiene_cocina tip_prepara_alimentos tip_uso_cocina tip_energia_cocina ind_tiene_nevera ind_tiene_lavadora ind_tiene_pc ind_tiene_internet ind_tiene_moto ind_tiene_tractor ind_tiene_carro ind_tiene_bien_raiz ind_gasto_alimento exp_food ind_gasto_transporte exp_transport ind_gasto_educacion exp_education ind_gasto_salud exp_health ind_gasto_serv_publicos exp_public_services ind_gasto_celular exp_cellphone ind_gasto_arriendo exp_rent ind_gasto_otros exp_other total_expenditure num_habita_vivienda num_personas_posibles n_members fec_nacimiento edad_calculada tip_estado_civil ind_conyuge_vive_hogar ide_conyuge ind_padre_vive_hogar tip_seg_social ind_tuvo_hijos tip_cuidado_niños ind_recibe_comida ind_leer_escribir ind_estudia niv_educativo grado_alcanzado ind_fondo_pensiones tip_actividad_mes num_sem_buscando tip_empleado grupo nivel clasificacion dup grupo_sf nivel_sf clasificacion_sf cod_dpto_sf cod_clase_sf region_sf urb_rur_sf sexo_persona_sisben tip_parentesco_sf ind_estudia_sf ind_ingr_estado_sf asiste_3_17_sf recibe_subs_sf sampling_frame age_feb20 age_jun20 age_sep20 male female wc_flush wc_pit wc_unconnected wc_letrine wc_no floor_finished floor_brick floor_concrete floor_badwood floor_dirt floor_other wall_finished wall_adobe wall_bahareque wall_prefabricated wall_badwood wall_vegetal wall_zinc wall_no members_room members_bedroom members_bedroom_exc kitchen cookpower_finished cookpower_electric cookpower_natgas cookpower_popgas cookpower_petrol cookpower_carbon cookpower_firewood cookpower_no cookpower_electric_missing cookpower_natgas_missing cookpower_popgas_missing cookpower_petrol_missing cookpower_carbon_missing cookpower_firewood_missing electricity electricity_missing own_fridge own_washing_machine own_PC own_internet own_moto own_tractor own_car own_real_state married free_union in_couple divorced single parents sewerage natural_gas trash_collection running_water water_7days water_7days_missing water_24h water_24h_missing water_drink_tap water_drink_boil water_drink_chlorine water_drink_filter water_drink_decanted water_drink_bottle trash_service trash_buried trash_burn trash_throw trash_water trash_informal trash_other shared_kitchen shared_kitchen_missing d_exp_food d_exp_food_missing d_exp_transport d_exp_transport_missing d_exp_education d_exp_education_missing d_exp_health d_exp_health_missing d_exp_public_services d_exp_public_services_mis d_exp_cellphone d_exp_cellphone_missing d_exp_rent d_exp_rent_missing d_exp_other d_exp_other_missing grade_attained pre_school primary middleschool highschool tecnico university postgrade no_education no_edu elementary tertiary_edu children works_0 act_jobseeking act_study act_stayhome act_rentier act_pensioner act_disabled act_noactivity weeks_jobseeking ocp_employee_priv ocp_employee_gov ocp_employee_dom ocp_independent_prof ocp_independent_worker ocp_bussinesowner ocp_parcel ocp_unpaid ocp_jornalero SS_contributive SS_special SS_subsidised SS_no SS_missing pension_fund pensioner inc1 inc_t cutoff_fe pc_exp pc_exp_q exp_q1 exp_q2 exp_q3 exp_q4 exp_q5 exp_q6 exp_q7 exp_q8 exp_q9 exp_q10 iassets iquality  m_surv
 glo sisben_labour informal_work formal_work
 glo sisben_inc l_pp_inc ihs_pp_inc inc_nt_t_w inc_t_w pp_inc pp_inc_nt ind_ingr_salario inc_wage ind_ingr_honorarios inc_fees ind_ingr_cosecha num_mes_ingr_cosecha inc_harvest ind_ingr_pension inc_pension ind_ingr_remesa_pais inc_remittance_local ind_ingr_remesa_exterior inc_remittance_inter ind_ingr_arriendos inc_rent ind_otros_ingresos inc_other ind_ingr_estado inc_familias_accion inc_col_mayor inc_other_subsidies 
 glo refpoints  epovline_depto_2019 epovline_city_2019 epovline_depto_2020 epovline_city_2020 epovline_depto_2021 epovline_city_2021 povline_depto_2019 povline_city_2019 povline_depto_2020 povline_city_2020 povline_depto_2021 povline_city_2021 nat_pvl_2019 nat_rural_pvl_2019 nat_epvl_2019 nat_rural_epvl_2019 nat_pvl_2020 nat_rural_pvl_2020 nat_epvl_2020 nat_rural_epvl_2020 nat_pvl_2021 nat_rural_pvl_2021 nat_epvl_2021 nat_rural_epvl_2021 ppp_2019 ppp_2020 ppp_2021

 *variables tracking data management
 glo data_track m_assetsA m_assetsB m_losses
 

 ** store time invariant variables
 
 preserve 
 
 keep id_vivienda_SISBEN $ids $surv_meta $housing $dems $other_assets $other_losses /*$sisben*/ /*$sisben_inc*/ /*$sisben_labour*/ $refpoints $data_track
 // SISBEN variables are restriced admin data
 
 tempfile tinv 
 save `tinv' 
 
 restore
 

 *--------------- 2.2.1. reshape time varing variables
 
 ** store labels
 unab vars : *2019
 local stubs : subinstr local vars "2019" "", all
 glo tvar "`stubs'"
 
 foreach v of local vars {
 	
	loc vlab : var label `v'
	local vlab:  subinstr local vlab "- 2019" "", all
	local vlab:  subinstr local vlab "2019" "", all
	
	local stubs : subinstr local v "2019" "", all
	loc l`stubs' "`vlab'"	
 }
 
 ** reshape by year and append
 forv y = 2019/2021 {
 	preserve
 	
	unab vars : *`y'
	keep `vars' id_vivienda_SISBEN
	local stubs : subinstr local vars "`y'" "", all
	di "`stubs'"
	reshape long `stubs', i(id_vivienda_SISBEN) j(year)
	
	tempfile long`y'
	if (`y' > 2019)		app using `long`=`y'-1''
	save `long`y''
	
	restore 
	if (`y' == 2021) 	use `long`y'', clear
 }

 ** recover lost labels 
 foreach v of global tvar {
 	
	lab var `v' "`l`v''"
	
 }
 
 * clean stubs 
 rename *_ *
 
 * add time invariant data
 merge m:1 id_vivienda_SISBEN using `tinv'
 
 /*
  Result                      Number of obs
    -----------------------------------------
    Not matched                             0
    Matched                            12,147  (_merge==3)
    -----------------------------------------
 */
 
 
 // PEnding better odering (just a lit bit)
 
 
 *============= 2.3: Some data transformations 

 *--------------- 2.3.1.  formal labour 
 
 gen work_formal = work_p==1 & pension==1 if !missing(work_p) & !missing(pension)
 lab var work_formal "Paid formal work"

 gen work_informal = work_p==1 & pension==0 if !missing(work_p) & !missing(pension)
 lab var work_informal "Paid informal work"
 
 /*==================================================
 			3: Final save
 ==================================================*/
 
 
 *----------3.1: Final save 
 
 save "${dir3r}/01_survey/replica/survey_targeting_r1_long.dta" , replace
  
 
exit
/* End of do-file */

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

Notes:
1.
2.
3.

